clear all
set type double

use "../data/mlitbid_090122a.dta", clear
cap drop _merge

*generate random vars to sort tie bids
set seed 1
gen r1 = runiform()
gen r2 = runiform()
gen r3 = runiform()

gen second_round=0
replace second_round = 1 if (n_bd_rd2 !=.)/* | n_bd_rd2~= "0")*/
replace second_round = 0 if (n_bd_rd2== 0)
sort id
by id: egen second_round2=max(second_round)
drop second_round
rename second_round2 second_round
encode nm_firm, gen(n_nm_firm)

gen double reserve = pr_reserve
rename n_pr_low lowlimit
count if 5* reserve<lowlimit & lowlimit ~=. 
replace reserve= 10 * reserve if 5* reserve<lowlimit & lowlimit ~=. // error in entry.

gen double tend_date = year_bid * 10000 + mo_bid * 100 + day_bid
gen double cont_date = year_con * 10000 + mo_con * 100 + day_con

/* 消費税の調整コード */
	/* BidがReserveよりも低いにも関わらず、Second roundに行くObservationが有る。
	これを見てみると、region=6と7について、2005年3月31日以前は、税込みで入っている可能性が高いと判明。
	そこで、下で当該オークションについて1.05で割ることとした。
	なお、割った結果小数点になるものがあったので、それはFLAG_decimalを立てた。	*/ 
 	capture drop temp 
 	gen double temp = reserve / 1.05 if cont_date <= 20050331 & (region == 6 | region == 7)
 	replace reserve = temp if cont_date <= 20050331 & (region == 6 | region == 7) 
 	gen FLAG_decimal = 1 if cont_date <= 20050331 & (region == 6 | region == 7) & mod(temp, 1) != 0
	drop temp 

/*generate numerical score_n & nd_score_n & rd_score_n*/ 
	gen double aaa=  sc_tech / n_bd_rd1
	rename aaa score_n

/*change scor-tend from 0 to 1 if the auction has a recorded score*/
	bysort id: egen aaa = min(score_n)
	bysort id: replace scor_tend = 1 if aaa ~= .
	drop aaa

/*generate date*/
	gen mydate = mdy(mo_bid,day_bid,year_bid)
	gen date2005 = mydate - 16437	/*note: Jan 1, 2005 = 0*/
	drop mydate
	
/*generate date*/
	gen mydate = mdy(mo_con,day_con,year_con)
	gen date2005_ct = mydate - 16437	/*note: Jan 1, 2005 = 0*/
	drop mydate


/* gen fiscal year (fyear)*/
gen fy_bid = year_bid
replace fy_bid = fy_bid - 1 if mo_bid >= 1 & mo_bid <= 3

/*generate rank of the first round just by price - track ties, */
set type double
gen s_bd_rd1 = n_bd_rd1 -  r1 / 1000
gen s_score_n = score_n - score_n / 10000000000 * r2
gen s_tech_n = sc_tech - sc_tech / 10000000000 * r3
drop r1 r2 r3

gsort id s_bd_rd1
set type double

/*generate rank of the second round just by price*/
	by id: egen rank1_p= rank(s_bd_rd1) if n_bd_rd1 > 0 & n_bd_rd1 ~= . ,u
	drop s_bd_rd1 
	
/*generate rank of the second round just by nonprice_attributes*/
	by id: egen rank1_q= rank(-s_tech_n) if s_tech_n >= 100 & s_tech_n ~= . ,u
	drop s_tech_n

/*generate score-rank of the first round*/
	by id: egen rank1_s= rank(-s_score_n) if score_n > 0,u
	drop s_score_n 

/*generate rank1 (in price for the price-only auction; in score for the scoring auction)*/
	gen rank1 = rank1_p
	replace rank1 = rank1_s if scor_tend== 1
	
/*generate a variable corresponding to the bid value as a percent of the reserve of the first round*/
	cap drop percent_reserve*
	gen percent_reserve = n_bd_rd1/ reserve
/*generate a variable corresponding to the n-th bid value as a percent of the reserve of the first round*/

forvalues x = 1(1)3{
	set type double
	cap drop percent_reserve`x'
	by id: egen percent_reserve`x' = max(percent_reserve * (rank1_p==`x') )
	replace percent_reserve`x' = . if percent_reserve`x' == 0
}

/*generate a var corresponding to the score as a percent of the base score (100) of the first round*/
/*a variable corresponding to the x-th tech point */

forvalues x = 1(1)3{
	set type double
	cap drop score_n`x'
	bysort id: egen score_n`x' = max(score_n * (rank1_p == `x') )
	replace score_n`x' = . if score_n`x' == 0
	
	cap drop score_ns`x'
	bys id: egen score_ns`x' = max(score_n * (rank1_s == `x') )
	replace score_ns`x' = . if score_ns`x' == 0
	
	/*cap drop ns_sc_tech`x'
	bys id: egen ns_sc_tech`x' = max( sc_tech * (rank1_s == `x') )
	replace ns_sc_tech`x' = . if ns_sc_tech`x' == 0

	cap drop np_sc_tech`x'
	bys id: egen np_sc_tech`x' = max( sc_tech * (rank1_p == `x') )
	replace np_sc_tech`x' = . if np_sc_tech`x' == 0*/

	cap drop price_s`x'
	bys id: egen price_s`x' = max(percent_reserve * (rank1_s == `x') )
	replace price_s`x' = . if price_s`x' == 0
}


forvalues x = 1(1)2{
	set type double
	cap drop *delta`x'*
	local y = `x' + 1
	*cap drop delta`x'
	/*gen delta`x' = percent_reserve - percent_reserve`x' ///
		if rank1_p > `x' & rank1_p < .
	replace delta`x' = percent_reserve - percent_reserve`y' ///
		if rank1_p == `x'*/
	gen ldelta`x'p_p = (percent_reserve - percent_reserve`x')/percent_reserve`x' ///
		if rank1_p > `x' & rank1_p < .
	replace ldelta`x'p_p = (percent_reserve - percent_reserve`y')/percent_reserve  ///
		if rank1_p == `x'
	gen ldelta`x'p_s = (percent_reserve - price_s`x')/price_s`x' ///
		if rank1_s > `x' & rank1_s < .
	replace ldelta`x'p_s = (percent_reserve - price_s`y')/percent_reserve  ///
		if rank1_s == `x'
	
	/* normalized by the pctbid of rank`x' 
			so that, for example, delta1x is completely symmetric if rank1 < 3*/
	cap drop ldelta`x's_p
	gen ldelta`x's_p = (score_n - score_n`x') / score_n`x' ///
		if rank1_p > `x' & rank1_p < .
	replace ldelta`x's_p = (score_n - score_n`y') / score_n ///
		if rank1_p == `x'
	cap drop ldelta`x's_s
	gen ldelta`x's_s = (score_n - score_ns`x') / score_ns`x' ///
		if rank1_s > `x' & rank1_s < . 
	replace ldelta`x's_s = (score_n - score_ns`y') / score_n ///
		if rank1_s == `x'
	/*	
	cap drop ldelta`x't_s
	gen ldelta`x't_s = (sc_tech - ns_sc_tech`x') / ns_sc_tech`x' ///
		if rank1_s > `x' & rank1_s < .
	replace ldelta`x't_s = (sc_tech - ns_sc_tech`y') / sc_tech ///
		if rank1_s == `x'*/
}


	cap drop win_bid_pct winner
	gen double win_bid_pct=winbid/reserve if winbid <= reserve
	gen double winner = 1 if  regexm(note, ".*決定.*") ==1 | (regexm(note, ".*落札.*")== 1 & regexm(note, ".*不落.*")==0 & regexm(note, ".*解除*.") == 0)
	by id: egen rakusatsu = sum(winner)
	tab rakusatsu
*br if rakusatsu > 1


/* want to FLAG problematic data */
*gen FLAG=0
replace FLAG=1 if second_round==1& percent_reserve1<=1 //1st round low bid < reserve, but there is a scond round.
replace FLAG=1 if percent_reserve1>2 & percent_reserve1~=. //probably typo
replace FLAG=1 if aucform == 9 //Zuii-Keiyaku
replace FLAG=1 if aucform == 5 // Tanka-keiyaku in project name
by id: egen maxpercent_reserve=max(percent_reserve)
replace FLAG=1 if maxpercent_reserve>2
replace FLAG = 1 if rakusatsu ~= 1 // drop auctions in which there are 2 winners or 0 winner
*replace FLAG = 1 if jitai == 1
*************************************************

/* create blogx capx utilx
	cap drop blog_temp
	cap drop blog*0
	forvalues x = 360(360)720 {
		di "####### Creating blog`x' ######"
		gen blog_temp = winner * (1-FLAG) * win_bid_pct*reserve
		replace blog_temp = 0 if blog_temp==. 

		expand 2 if winner == 1 & FLAG == 0, gen(expand)
		replace blog_temp = -blog_temp if expand == 1
		replace date2005 = date2005 + `x' if expand == 1
		replace id = -id if expand == 1
		*ソートしたとき各オークション日の最初にくるようにする。そうでないと、オークションによっては1年前の案件の償却が反映されない。
		sort firmregID date2005 id
		*オークションIDの若いほうが最初にくるようにする。
		by firmregID: replace blog_temp = blog_temp + blog_temp[_n-1] if firmregID == firmregID[_n-1]
		replace blog_temp = blog_temp - win_bid_pct*reserve if winner == 1
		/* 受注日のblog_tempには受注日の契約は含まない*/
		drop if expand == 1
		
		*Drop obs. with blog_tempclose to 0 (double)
		replace blog_temp = 0 if blog_temp < 1 & blog_temp !=.

		by firmregID date2005: egen blog_x = min(blog_temp)
		replace blog_temp = blog_x
		drop blog_x expand
		*もし同日に複数回オークションに勝ったら、オークションによっては同日に勝った別のオークションがバックログに入ってしまう。
		*するとソートのされ方によってUtilが変わる。
		*同日に勝ったオークションはバックログに入れない。
		rename blog_temp blog`x'
	}

/* create utilnm: (blogx - average backlog) / sd_backlog*/
	foreach x in 360 720{
		cap drop avrblog`x'
		cap drop sdblog`x'
		cap drop utilnm`x'
		bys firmregID: egen avrblog`x' = mean(blog`x')
		bys firmregID: egen sdblog`x' = sd(blog`x')
		gen utilnm`x' = (blog`x' - avrblog`x')/sdblog`x'
	}
	*/
	
/* drop FLAGGED data*/
replace FLAG = 1 if nb == 1

/*change firm_name ad_firm*/
*replace nm_firm = "西条道路（株）" if nm_firm == "西條道路舗装（株）"

/* create date from winning 
	sort firmregID date2005 id
	* bidder毎に時系列でソートする
	cap drop ord
	by firmregID: gen ord=_n
	* bidder毎に順番をつける
	cap drop windate
	gen windate=date2005 if winner==1
	* 勝った日をつける
	replace windate=windate[_n-1] if ord>1 & winner~=1
	* 勝たなかった日に関しては最後に勝った日付をいれる
	cap drop date_from_win
	gen date_from_win=date2005-windate if winner~=1
	* 勝たなかった日に関しては最後に勝った日からの経過日数を入れる
	replace date_from_win=date2005-windate[_n-1] if ord>1 & winner==1
	* 勝った日に関しても前回に勝った日からの経過日数を入れる。
	cap drop dates_after_win
	gen dates_after_win=1 if date_from_win~=.
	drop ord windate date_from_win*/

	/* check*/
	order nm_firm ad_firm 
	format %40s ad_firm 
	cap drop firm*ID*
	cap drop _merge
	drop dup*
	*drop *_rd2 *_rd3 *_rd4 *_rd5

	drop pr_reserve 
	drop  n_nm_firm 
	drop FLAG_decimal
	drop rakusatsu maxpercent_reserve
	drop du_contract
	compress

	save "../tmp/temp_biddata_090122.dta", replace
*tab year_con region if ad_firm ~= "" | no_license < .
